<?php

class MySQLRDO extends RDO {

    public function __construct($host, $database, $username = "", $password = "") {
        $url = "mysql:dbname=$database;host=$host";
        parent::__construct($url, $username, $password);
    }

    //Insert from 1 RTO
    public function insert(&$rto) {
        $table = $rto->getNamespace() . ".`" . $rto->getName()."`";
        $arr = get_object_vars($rto->newRTO());

        if ($arr === null || count($arr) == 0 || $table === null || $table == "") {
            return false;
        } else {
            $columns = "";
            $params = "";
            $key = array_keys($arr);
            foreach ($key as $index => $k) {
                if ($key != $rto->getKeyAI()) {
                    if ($index == 0) {
                        $columns.="`$k`";
                        $params.=":$k";
                    } else {
                        $columns.=",`$k`";
                        $params.=",:$k";
                    }
                }
            }

            $sql = "INSERT INTO $table($columns) VALUES($params)";
            $stmt = $this->prepare($sql);
            foreach ($key as $value) {
                $rs = $rto->$value;
                if (is_string($rs)) {
                    $rs = trim($rs);
                }
                $stmt->bindParam(':' . $value, $rs);
            }
            $stmt->execute();
            if ($rto->getKeyAI() !== null) {
                $id_newest = $this->lastInsertId();
                if ($id_newest !== null) {
                    $keyAutoIncrement = $rto->getKeyAI();
                    $rto->$keyAutoIncrement = $this->lastInsertId();
                } else {
                    return false;
                }
            }
          
            return true;
        }
    }

    //Delete from 1 RTO
    public function delete(&$rto) {
        $table = $rto->getNamespace() . ".`" . $rto->getName()."`";
        $column = $rto->getPrimaryKey();
        if ($column === null || $table === null || $table == "" || count($column) == 0) {
            return false;
        } else {
            $sql = "DELETE FROM $table WHERE 1=1 ";
            foreach ($column as $key => $value) {
                $sql .= " AND `$key` = :$key";
            }
            $stmt = $this->prepare($sql);
            foreach ($column as $key => $value) {
                $stmt->bindParam(':' . $key, $value);
            }
            $stmt->execute();
            return true;
        }
    }

    //Update from 1 RTO
    public function update(&$rto) {
        $table = $rto->getNamespace() . ".`" . $rto->getName()."`";
        $column = $rto->getPrimaryKey();
        $arr = get_object_vars($rto->newRTO());

        if ($arr == null || $column === null || $table === null || $table == "" || count($arr) == 0 || count($column) == 0) {
            return false;
        } else {
            $sql = "UPDATE $table SET ";
            $flag = 0;
            
            foreach ($arr as $key => $value) {
                
                if (!isset($column[$key]) && $rto->$key !== null) { 
                    if ($flag == 0) {
                        $flag = 1;
                        $sql.="`$key`=:$key";
                    } else {
                        $sql.=",`$key`=:$key";
                    }
                }
            }
            
            $sql .= " WHERE 1=1 ";

            foreach ($column as $key => $value) {
                $sql.=" AND `$key`=:$key";
            }

            $stmt = $this->prepare($sql);
            foreach ($arr as $key => $value) {
                $rs = $rto->$key;
                if (!isset($column[$key]) && $rs !== null) {
                    if (!is_object($rs)) {
                        if (is_string($rs)) {
                            $rs = trim($rs);
                        }
                        $stmt->bindParam(':' . $key, $rs);
                    } else {
                        $stmt->bindParam(':' . $key, null);
                    }
                }
            }
            foreach ($column as $key => $value) {
                $stmt->bindParam(':' . $key, $value);
            }
            $stmt->execute();
            return true;
        }
    }

    //mapping object by primary key
    public function map(&$rto) {
        $table = $rto->getNamespace() . ".`" . $rto->getName()."`";
        $column = $rto->getPrimaryKey();
        if ($column !== null && $table !== null && $table != "" && count($column) > 0) {
            $sql = " SELECT $table.* FROM $table WHERE 1=1";

            foreach ($column as $key => $value) {
                $sql.=" AND `$key`=:$key";
            }

            $stmt = $this->prepare($sql);
            foreach ($column as $key => $value) {
                $stmt->bindParam(':' . $key, $value);
            }
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            if (isset($result[0])) {
                foreach ($result[0] as $key => $value) {
                    $rto->$key = $value;
                }
            }
        }
    }
    
    //check existed
    public function isExisted($table,$columns){
        if ($table !== null && $table != "" && count($columns) > 0 ) {
            $condition = '';
            foreach ($columns as $key => $value) {
                if(is_string($value)){
                    $condition .= ($condition == '' ? $key." = '".$value."'" : " AND ".$key." = '".$value."'" );
                }else{
                    $condition .= ($condition == '' ? $key." = ".$value : " AND ".$key." = ".$value );
                }
            }
            $stmt = $this->prepare("SELECT EXISTS(SELECT 1 FROM $table WHERE  $condition )");
            $stmt->execute();
            $result = $stmt->fetch();
            if (isset($result[0]) && $result[0] == 1) {
                return true;
            }
        }
        return false;
    }

    //Convert sql statement to count statement
    public function convertToCountStatement($sql) {
        $query = trim($sql);
        $len = strlen($query);
        $countSelect = 0;
        $countFrom = 0;
        $start = 0;
        $end = 0;
        $countFunc = " COUNT(*) ";

        for ($i = 0; $i < $len; $i++) {
            //find SELECT

            if (($i + 6) < $len
                    && strtolower($query[$i]) == 's'
                    && strtolower($query[$i + 1]) == 'e'
                    && strtolower($query[$i + 2]) == 'l'
                    && strtolower($query[$i + 3]) == 'e'
                    && strtolower($query[$i + 4]) == 'c'
                    && strtolower($query[$i + 5]) == 't') {

                $countSelect++;
                $i+=5;
                if ($start == 0) {
                    $start = $i + 1;
                }
            }
            //find FROM
            else if (($i + 4) < $len
                    && strtolower($query[$i]) == 'f'
                    && strtolower($query[$i + 1]) == 'r'
                    && strtolower($query[$i + 2]) == 'o'
                    && strtolower($query[$i + 3]) == 'm') {
                $countFrom++;
                $end = $i;
                $i+=3;
            }
            if ($countSelect == $countFrom) {
                $i = $len;
            }
        }

        if ($start != 0 && $end != 0) {
            $section1 = substr($query, 0, $start);
            $section2 = substr($query, $end, $len - $end);
            return $section1 . $countFunc . $section2;
        }
        return "";
    }

}
